-- Given a HierarchyKey returns the full path to the document starting with the root name.  The path is in
-- slash notation.  Like $/Common/Business Objects/DocumentType
CREATE PROCEDURE [dbo].[asi_DocumentPathByHierarchyKeyOut]
   @hierarchyKey uniqueidentifier,
   @path nvarchar(4000) OUT,
   @excludeDocument bit = 0,
   @publishedOnly bit = 0
AS
BEGIN
DECLARE
   @rootHierarchyKey uniqueidentifier,
   @documentFound bit,
   @continue int

   SET NOCOUNT ON

   SET @path = ''
   SET @documentFound = 0
   SET @continue = 1

   SELECT @rootHierarchyKey = RootHierarchyKey
     FROM Hierarchy
    WHERE HierarchyKey = @hierarchyKey

   WHILE @hierarchyKey is not null AND @hierarchyKey <> @rootHierarchyKey AND @continue > 0
   BEGIN
      SELECT TOP 1
             @path = DocumentMain.DocumentName + CASE WHEN LEN(@path) > 0 THEN '/' + @path ELSE '' END,
             @hierarchyKey = Hierarchy.ParentHierarchyKey,
             @rootHierarchyKey = Hierarchy.RootHierarchyKey
        FROM Hierarchy INNER JOIN DocumentMain on Hierarchy.UniformKey = DocumentMain.DocumentVersionKey
       WHERE Hierarchy.HierarchyKey = @hierarchyKey
         AND (DocumentMain.DocumentStatusCode IN (40,60) OR @publishedOnly = 0)
       ORDER BY DocumentMain.CreatedOn DESC

      SET @continue = @@ROWCOUNT

      IF @documentFound = 0 AND @excludeDocument = 1
      BEGIN
         SET @documentFound = 1
         SET @path = ''
      END
   END

   SET NOCOUNT OFF

   SELECT @path = HierarchyRootName + CASE WHEN LEN(@path) > 0 THEN '/' + @path ELSE '' END
     FROM HierarchyRoot
    WHERE RootHierarchyKey = @rootHierarchyKey
END

GO